/*
 * Copyright (C) 2011 Young,Fey <fey.young@gmail.com>
 * 
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 * 
 */

package com.youngfey.sqlite
import groovy.util.logging.*
import groovy.transform.*
import static java.sql.DatabaseMetaData.*

/**
 *
 * @author Young,Fey <fey.young@gmail.com>
 */
@Log
class Table {

    /**
     * @param name 表名
     * @param sqlite SQLite数据库，默认为null
     * @param stmt DDL语句，默认为null
     * @param loadColumns 是否立刻加载表结构
     * @param loadData 是否立刻加载表数据
     */
    def Table(String name, SQLite sqlite = null, String stmt = null, 
            boolean loadColumns = false, boolean loadData = false) {
        verify name
        this.name = name
        this.stmt = stmt
        this.sqlite = sqlite
        if(loadColumns) this.loadColumns()
        if(loadData) this.loadData()
    }

    private verify = { String name ->
        assert name, 'name couldn\'t be null or empty'
        //TODO: 使用正则表达式验证表名
    }

    /**
     * @return 返回表的结构，如果columns为空则尝试加载
     * 注意当手动生成Table对象并尝试addColumn后，columns可能是不完整的
     * 结构，可以通过inferColumns推测可能的表结构
     */
    Map<String, Column> getColumns() {
        if(!columns) {
            loadColumns()
        }
        columns
    }

    /**
     * 添加列
     * 当表已经在数据库中存在的情况下，不允许手动添加列
     * 不允许添加重复的列
     */
    void addColumn(Column c) {
        log.fine "Try to add column '$c' to table '$name'"
        if(stmt) {
            log.warning "Try to add column '$c' to existed table '$name', do noting"
            return
        }
        assert c, "Column couldn't be null"
        //由于SQLite.addTable()一定会触发SQLite.load()，因此，任何已存在的
        //表都不可能允许addColumn()方法，因此，该方法必然只能应用于新建表
        //的过程中。因此无需调用loadColumn()过程
        assert !columns?.containsKey(c.name), "Duplicated column: $c"
        columns.put(c.name, c)
    }

    /**
     * 返回所有行，如rows为空则尝试加载
     */
    List<Row> getRows() {
        if(!rows) loadData()
        rows
    }

    /**
     * 返回表的行数
     */
    int size() { getRows().size() }

    /**
     * 将行转换为Map数据后返回
     */
    Map<String, List> toMap() {
        [(name): rows.collect { it.toMap() }]
    }


    /**
     * 将一行数据插入到队列中
     * 注意在commit()前，数据不会被写入到文件
     */
    void insert(Row row) {
        if(queue == null) queue = []
        queue << row
    }

    /**
     * 将一组数据插入到队列中
     * 注意在commit()前，数据不会被写入到文件
     */
    void insert(List<Row> rows) {
        if(queue == null) queue = []
        queue += rows
    }

    /**
     * 将一组Map形式的数据插入到队列中
     * 注意在commit()前，数据不会被写入到文件
     */
    void insertMaps(List<Map> maps) {
        insert(Row.getInstances(maps))
    }

    /**
     * 将insert队列中的数据写入文件
     */
    void commit() {
        log.fine "Try to commit: $name"
        //仅对于制定需要重建的表或不存在的表调用recreate过程
        if(recreate || !sqlite.hasExistedTable(name)) recreateTable()
        def dataSet = sqlite.dataSet(name)
        //此处需调用loadColumn过程，因为对于手动创建但并未addTable()
        //的表而言，可能对应着已存在的表
        loadColumns()
        //在这里并没有对queue进行分批提交，理由是SQLite应用于移动数据，可以假
        //设其规模受到限制。在测试用18.8万行的数据进行了测试，其速度可以接受。
        //已知在7200rpm的硬盘和普通PC机上SQLite的insert速度约为5000次/s，提交
        //速度约为60次/s，预期18.8万行的数据耗时小于50s。
        queue.each { dataSet.add(it.toMap(columns)) }
        dataSet.commit()
        queue = null
        loadData()
    }

    /**
     * 重新创建表
     */
    void recreateTable() {
        log.fine "Try to recreate table: $name"
        try {
            rows = null
            String drop = "DROP TABLE IF EXISTS $name"
            sqlite.execute(drop)
            sqlite.execute(stmt ? stmt : genStmt())
            loadColumns()
        } catch(e) {
            log.severe "Recreate Table failed: $name"
            throw e
        }
    }

    private String genStmt() {
        inferColumns()
        "CREATE TABLE $name ( ${columns.values().collect { it.genStmt() }.join(', ') })" 
    }

    //推测表结构，queue中所有的键值都会被作为column name处理
    //对于名称相同的键，其值可能是不同的DataType数据类型，选择数量最多的类型
    //作为Column.type
    private inferColumns() {
        log.config "Try to infer columns of '$name'"
        if(!queue) return
        if(!columns) columns = [:]
        def map = [:].withDefault { [:].withDefault { 0 } }
        queue.each { row ->
            row.toMap().each { c, v ->
                def dt = DataType.conv(v.getClass())
                def dts = map.get(c)
                dts.put(dt, dts.get(dt) + 1) 
            }
        }

        map.each { c, f ->
            if(!columns.containsKey(c)) columns.put(c, new Column(c)) 
            columns.get(c).type = f.max { it.value }.key
        }
        log.fine "Inferred columns:\n${columns.values().join('\n')}"
    }

    //此处使用metaData加载列定义
    //在sqlitejdbc驱动中，未提供is_autoincrement列，其data_type亦不返回有效值
    private loadColumns() {
        log.fine "Try to load columns: $name"
        try {
            columns = [:]
            if(!sqlite) {
                log.fine 'sqlite is null, do nothing'
                return
            }
            def rs = sqlite.connection.metaData.getColumns(null, null, name, '%')
            while(rs.next()) {
                def row = rs.toRowResult()
                log.finer """${row.each { k, v -> "$k: $v" } }"""
                final columnName = row.COLUMN_NAME
                final dataType = DataType.getInstance(row.TYPE_NAME)
                def nullable 
                switch(row.NULLABLE) {
                    case columnNoNulls: nullable = false; break
                    case columnNullable: nullable = true; break
                    case columnNullableUnknown:
                    log.warning "Column $columnName Nullable UNKNOWN"
                    break
                    default:
                    log.severe "Column $columnName Nullable: $rs.NULLABLE"
                }
                columns.put(columnName,
                    new Column(columnName, nullable, null, dataType))
            }
            log.fine "Columns loaded: \n${columns.keySet()}"
        } catch(e) {
            log.severe "Load Columns: $name\n$e"
            throw e
        }
    }

    private loadData() {
        if(!columns) loadColumns()
        try {
            log.fine "Try to load data: $name"
            rows = []
            sqlite.sql.dataSet(name).rows().each { r ->
                def row = new Row()
                columns.keySet().each { columnName ->
                    row.put(columnName, r.getAt(columnName))
                }
                this.@rows << row 
            }
            log.finest "Data loaded: $name\n${rows.join('\n')}"
        } catch(e) {
            log.severe "Load Data: $name\n$e"
        }
    }

    final String name
    List<Row> rows
    Map<String, Column> columns
    String stmt

    boolean recreate = false
    private queue
    private sqlite
}

